﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using NPOI.HSSF.UserModel;
using System.Data.OleDb;
using PublishData.BLL;

namespace PublishData
{
    public partial class frmPublish : Form
    {
        public frmPublish()
        {
            InitializeComponent();
            KhoiTaoCot();
        }

        private void KhoiTaoCot()
        {
            soBN.DataPropertyName = "SBN";
            soDienThoai.DataPropertyName = "DIENTHOAI";
            maCV.DataPropertyName = "MACV";
            hoTen.DataPropertyName = "HOTEN";
            diaChi.DataPropertyName = "DIACHI";
            soTo.DataPropertyName = "SOTO";
            soThua.DataPropertyName = "SOTHUA";
            phuong.DataPropertyName = "PHUONG";
            maBXL.DataPropertyName = "MABXL";
            gridData.AutoGenerateColumns = false;
            gridData.AllowDrop = false;
            gridData.AllowUserToDeleteRows = false;
            gridData.AllowUserToAddRows = false;
        }

        private void bntBrowse_Click(object sender, EventArgs e)
        {
            OpenFileDialog fd = new OpenFileDialog();
            fd.Filter = "(*.xls;*xlsx)|*.xls;*.xlsx";
            if (fd.ShowDialog() == DialogResult.OK)
            {
                txtPathExcel.Text = fd.FileName;
                NapDanhSachCacSheet();
            }
        }

        private void NapDanhSachCacSheet()
        {
            cmbSheet.Items.Clear();
            var ls = new ExcelImporter(txtPathExcel.Text).DanhSachSheet();
            foreach (var item in ls)
            {
                var item2 = item.Replace("'", "");
                if (item2.EndsWith("$Print_Titles")
                    || item2.EndsWith("$_FilterDatabase") || item2.EndsWith("#_FilterDatabase")
                    || item2.EndsWith("_"))
                    continue;
                item2 = item2.Replace("$", "");
                cmbSheet.Items.Add(item2);
            }
        }

        private void bntPreview_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(txtPathExcel.Text))
            {
                LoadDataForPreview();
            }
            else
                MessageBox.Show("Bạn phải chọn file trước", "Chú ý", MessageBoxButtons.OK, MessageBoxIcon.Warning);
        }

        private void LoadDataForPreview()
        {
            if (cmbSheet.SelectedIndex != -1)
            {
                bdsDsSv.DataSource = null;
                gridData.DataSource = bdsDsSv;
                string path = txtPathExcel.Text;
                string sheetName = cmbSheet.Text;
                DataTable dt = null;
                FormProgress frm = new FormProgress();
                frm.DoWork += delegate(object s, DoWorkEventArgs oE)
                {
                    dt = new ExcelImporter(path).LayDuLieuSheet(sheetName);
                };
                frm.RunWorkerCompleted += delegate(object s, RunWorkerCompletedEventArgs oE)
                {
                };
                frm.StartWorking(this);
                if (dt != null)
                {
                    var dsCot = dt.Columns;
                    bool checkColumn = CheckColumn(dsCot);
                    if (checkColumn)
                    {
                        bdsDsSv.DataSource = dt;
                        gridData.DataSource = bdsDsSv;
                        lbTongSo.Text = dt.Rows.Count.ToString();
                    }
                    else
                    {
                        MessageBox.Show("Định dạng trong sheet không hợp lệ (masinhvien, ho, ten)", "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                    }
                }
            }
            else
            {
                MessageBox.Show("Bạn phải chọn sheet trước", "Chú ý", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }

        bool CheckColumn(DataColumnCollection cl)
        {
            return cl.Contains("SBN") && cl.Contains("DIENTHOAI") && cl.Contains("MACV")
                && cl.Contains("HOTEN") && cl.Contains("DIACHI") && cl.Contains("SOTO")
                && cl.Contains("SOTHUA") && cl.Contains("PHUONG") && cl.Contains("MABXL");
        }
        int countThemMoi = 0;

        public int CountThemMoi
        {
            get { return countThemMoi; }
            set { countThemMoi = value; }
        }
        int countCapNhat = 0;

        public int CountCapNhat
        {
            get { return countCapNhat; }
            set { countCapNhat = value; }
        }
        int countNotChange = 0;

        public int CountNotChange
        {
            get { return countNotChange; }
            set { countNotChange = value; }
        }
        int countTongSo = 0;

        public int CountTongSo
        {
            get { return countTongSo; }
            set { countTongSo = value; }
        }
        private void bntImport_Click(object sender, EventArgs e)
        {

            DataTable dt = bdsDsSv.DataSource as DataTable;
            countTongSo = dt.Rows.Count;
            FormProgress frm = new FormProgress();
            frm.DoWork += delegate(object s, DoWorkEventArgs oE)
            {
                frm.CountTongSo = countTongSo;
                HoSoBLL hsBLL = new HoSoBLL();
                for (int i = 0; i < countTongSo; i++)
                {
                    var row = dt.Rows[i];
                    var sbn = row["SBN"].ToString();
                    if (!string.IsNullOrEmpty(sbn))
                    {
                        int result = hsBLL.update(sbn,
                                row["DIENTHOAI"].ToString(),
                               row["MACV"].ToString(),
                                row["HOTEN"].ToString(),
                                row["DIACHI"].ToString(),
                                row["SOTO"].ToString(),
                                row["SOTHUA"].ToString(),
                                row["PHUONG"].ToString(),
                                row["MABXL"].ToString());
                        if (result == 1)
                            countThemMoi++;
                        else if (result == 2)
                            countCapNhat++;
                        else if (result == 3)
                            countNotChange++;
                        frm.CountNumber = i;
                        //else
                        //    throw new Exception("Lỗi thêm mới với số biên nhận là: " + sbn);
                    }
                }
                hsBLL.Luu();
            };
            frm.RunWorkerCompleted += delegate(object s, RunWorkerCompletedEventArgs oE)
            {
                if (oE.Error != null)
                    MessageBox.Show(oE.Error.Message, "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
            };
            frm.StartWorking(this);
            MessageBox.Show(string.Format(@"Số lượng hồ sơ được thêm mới: {0}
Số lượng hồ sơ được cập nhật thông tin: {1}
Số lượng hồ sơ không thay đổi: {2}", countThemMoi, countCapNhat, countNotChange), "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }

        private void bdsDsSv_ListChanged(object sender, ListChangedEventArgs e)
        {
            lbTongSo.Text = bdsDsSv.List.Count.ToString();
        }

        private void cmbSheet_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cmbSheet.SelectedIndex != -1)
            {
                bntPreview.Enabled = true;
                bntImport.Enabled = true;
            }
            else
            {
                bntImport.Enabled = false;
                bntPreview.Enabled = false;
            }
        }
    }
}
